<?php

namespace Home\Controller;

use Think\Controller;


/**
 * 外包客服外呼中心
 */
class AchievementsController extends CommnController
{
    public function __construct()
    {
        parent::__construct();
    }

    // 外呼统计
    public function call_statistics()
    {
        $start = I('start_time');
        $end = I('end_time');
        $start_time = strtotime($start);
        $end_time = strtotime($end);
        $where['status'] = 1;
        $where['role_id'] = array('in', '2,15');
        $where['out_kf_id'] = 0;
        $admin = M('admin', '', 'KEFU')->where($where)->field('name,id')->order('role_id asc')->select();
        foreach ($admin as $k => $v) {
            $where1 = [];
            if (!empty($start) && !empty($end)) {
                $where1['ctime'] = array('between', array($start_time, $end_time));
            }
            $where1['admin'] = $v['name'];
            //通话总数
            $admin[$k]['total_call'] = M('call_info', '', 'KEFU')->where($where1)->count();
            //外呼总数
            $where1['calltype'] = 1;
            $admin[$k]['out_call'] = M('call_info', '', 'KEFU')->where($where1)->count();
            //外呼有效
            $where1['state'] = 1;
            $admin[$k]['effective_out_call'] = M('call_info', '', 'KEFU')->where($where1)->count();
            //呼入总数
            $where2 = [];
            $where2['admin'] = $v['name'];
            if (!empty($start) && !empty($end)) {
                $where2['ctime'] = array('between', array($start_time, $end_time));
            }
            $where2['calltype'] = 2;
            $admin[$k]['in_call'] = M('call_info', '', 'KEFU')->where($where2)->count();
            //呼入已接听
            $where1['state'] = 1;
            $where1['calltype'] = 2;
            $admin[$k]['get_in_call'] = M('call_info', '', 'KEFU')->where($where1)->count();
        }

        $this->assign(array(
            'data' => $admin,
            'start_time' => $start,
            'end_time' => $end,
            'test' => $admin
        ));
        $this->display();
    }

    //在线反馈统计
    public function online_statistics()
    {
        $start = I('start_time');
        $end = I('end_time');
        $start_time = strtotime($start);
        $end_time = strtotime($end);
        if (empty($start) && empty($end)) {
            $start_time = strtotime(date('Y-m-01', time()));
//            $end_time=strtotime(date('Y-m-t',time()));
            $end_time = time();
        }
        $admin = M('admin', '', 'KEFU_R')->where(['role_id' => 2, 'out_kf_id' => 0, 'status' => 1])->field('name,id')->select();
        foreach ($admin as $k => $v) {
            $where = [];
            $where['b.ctime'] = array('between', array($start_time, $end_time));
            $where['a.vid'] = array('eq', '0');
            $where['a.admin_name'] = array('eq', $v['name']);
            $field = 'b.ctime,a.ctime as back_time,b.vid,a.admin_name';
            $data = M('user_message', '', 'KEFU_R')->join('a join dxd_kefu.user_feedback b on b.id=a.uf_id')->field($field)->group('a.uf_id')->where($where)->select();
            $one_hour = 60 * 60;
            $over_count = 0;// 超时回复计数
            $admin[$k]['total_feedback'] = count($data);
            foreach ($data as $k1 => $v1) {
                //休息时间不计入，午休 11;30-13：30,  下班：20:00
                $feedback_time = $v1['ctime'];
                $day_time = strtotime(date('Y-m-d', $v1['ctime']));//用户反馈当天凌晨
                //非上班时间
                $nine = $day_time + 9 * 3600;//早上9点上班
                $lunch = $day_time + 11 * 3600 + 1800;//中午休息
                $afternoon = $day_time + 13 * 3600 + 1800;//中午休息
                $night = $day_time + 20 * 3600;//晚班下班
                if ($feedback_time < $nine) {//上班九点之前的 相当于9点的反馈，
                    $feedback_time = $nine;
                } else if ($feedback_time > $lunch && $feedback_time < $afternoon) {//午休时段的 相当于中午上班时候反馈
                    $feedback_time = $afternoon;
                } else if ($feedback_time > $night) {//晚班之后，相当于第二天9点的反馈
                    $feedback_time = $nine + 24 * 3600;
                }
                if ($feedback_time + $one_hour <= $v1['back_time']) {
                    $over_count++;
                }
            }
            $admin[$k]['over_count'] = $over_count;
        }
        $this->assign(array(
            'data' => $admin,
            'start_time' => $start,
            'end_time' => $end,
        ));
        $this->display();
    }

    //导出外呼统计
    public function excel_export()
    {
        $start = I('start_time');
        $end = I('end_time');
        $start_time = strtotime($start);
        $end_time = strtotime($end);
        $where['status'] = 1;
        $where['role_id'] = array('in', '2,15');
        $where['out_kf_id'] = 0;
        $admin = M('admin', '', 'KEFU')->where($where)->field('name,id')->order('role_id asc')->select();
        if (empty($admin)) {
            $this->ajaxReturn(array('code' => '1002', 'error' => '暂无记录需要导出！'));
            die;
        }

        require_once './excel/PHPExcel.php'; //导入php文件
        $filePath = './excel/call_info.xlsx'; //打开excel路径
        $PHPReader = new \PHPExcel_Reader_Excel2007(); //默认用excel2007读取excel
//        $this->ajaxReturn(array('code' => '1001'), 'json'); die;
        $PHPExcel = $PHPReader->load($filePath); //加载具体文件
        $lineNum = 2;
        foreach ($admin as $k => $v) {
            $where1 = [];
            if (!empty($start) && !empty($end)) {
                $where1['ctime'] = array('between', array($start_time, $end_time));
            }
            $where1['admin'] = $v['name'];
            //通话总数
            $total_call = M('call_info', '', 'KEFU')->where($where1)->count();
            $admin[$k]['total_call'] = $total_call;
            //外呼总数
            $where1['calltype'] = 1;
            $out_call = M('call_info', '', 'KEFU')->where($where1)->count();
            $admin[$k]['out_call'] = $out_call;
            //外呼有效
            $where1['state'] = 1;
            $effective_out_call = M('call_info', '', 'KEFU')->where($where1)->count();
            $admin[$k]['effective_out_call'] = $effective_out_call;
            //呼入总数
            $where2 = [];
            $where2['admin'] = $v['name'];
            if (!empty($start) && !empty($end)) {
                $where2['ctime'] = array('between', array($start_time, $end_time));
            }

            $where2['calltype'] = 2;
            $in_call = M('call_info', '', 'KEFU')->where($where2)->count();
            $admin[$k]['in_call'] = $in_call;
            //呼入已接听
            $where1['state'] = 1;
            $where1['calltype'] = 2;
            $get_in_call = M('call_info', '', 'KEFU')->where($where1)->count();
            $admin[$k]['get_in_call'] = $get_in_call;
            //写入表格
            $PHPExcel->getActiveSheet()->setCellValue('A' . $lineNum, $v['name']);
            $PHPExcel->getActiveSheet()->setCellValue('B' . $lineNum, $total_call);
            $PHPExcel->getActiveSheet()->setCellValue('C' . $lineNum, $out_call);
            $PHPExcel->getActiveSheet()->setCellValue('D' . $lineNum, $effective_out_call);
            $PHPExcel->getActiveSheet()->setCellValue('E' . $lineNum, $in_call);
            $PHPExcel->getActiveSheet()->setCellValue('F' . $lineNum, $get_in_call);
            $lineNum++;
        }
        $PHPExcel->getActiveSheet()->setTitle('订单记录');
        $PHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007');//保存类型
        $objWriter->save('./excel/call/call_info.xlsx');
        $url = 'http://nkf.jyyjrfw.com/excel/call/call_info.xlsx';//访问表格路径
//        $url = 'http://adm.nongchanlian.com/excel/order/order.xlsx';
        $this->ajaxReturn(array('code' => '1001', 'url' => $url), 'json');

    }

    /**
     *  反馈超时回复,绩效统计
     */
    public function feedback_statistics()
    {
        //所有客服
        $time = strtotime(date('Y-m-01', time()));
        $admin = M('admin', '', 'KEFU_R')->where(['role_id' => 2, 'out_kf_id' => 0, 'status' => 1])->field('name,id')->select();
        foreach ($admin as $k => $v) {
            $where = [];
            $where['b.ctime'] = array('egt', $time);
            $where['a.vid'] = array('eq', '0');
            $where['a.admin_name'] = array('eq', $v['name']);
            $field = 'b.ctime,a.ctime as back_time,b.vid,a.admin_name';
            $data = M('user_message', '', 'KEFU_R')->join('a join dxd_kefu.user_feedback b on b.id=a.uf_id')->field($field)->where($where)->select();
            $one_hour = 60 * 60;
            $over_count = 0;// 超时回复计数
            foreach ($data as $k1 => $v1) {
                //休息时间不计入，午休 11;30-13：30,  下班：20:00
                $feedback_time = $v1['ctime'];
                $day_time = strtotime(date('Y-m-d', $v1['ctime']));//用户反馈当天凌晨
                //非上班时间
                $nine = $day_time + 9 * 3600;//早上9点上班
                $lunch = $day_time + 11 * 3600 + 1800;//中午休息
                $afternoon = $day_time + 13 * 3600 + 1800;//中午休息
                $night = $day_time + 20 * 3600;//晚班下班
                if ($feedback_time < $nine) {//上班九点之前的 相当于9点的反馈，
                    $feedback_time = $nine;
                } else if ($feedback_time > $lunch && $feedback_time < $afternoon) {//午休时段的 相当于中午上班时候反馈
                    $feedback_time = $afternoon;
                } else if ($feedback_time > $night) {//晚班之后，相当于第二天9点的反馈
                    $feedback_time = $nine + 24 * 3600;
                }
                if ($feedback_time + $one_hour <= $v1['back_time']) {
                    $over_count++;
                }
            }
            $admin[$k]['over_count'] = $over_count;
        }
        $this->ajaxReturn(array('code' => '1001', 'data' => $admin));
    }

    /**
     * 应用市场统计
     */
    public function APP_market()
    {
//        $start=I('start_time');
//        $end=I('end_time');
//        $start_time=strtotime($start);
//        $end_time=strtotime($end);
//        $where=[];
//        if(!empty($start)  && !empty($end)){
//            $where['a.ctime']=array('between',array($start_time,$end_time));
//        }
////        $where['b.terrace']=array('exp','is not null');
//        $tol = M('user','','USER_R')->join('a join dxd_user.user_login b on b.vid=a.vid and b.terrace is not null')->where($where)->count();
//        $tol=intval($tol);
//        $row = '10';
//        $page = new PageController($tol, $row);
//        $fpage = $page->fpage();
//        $field="a.truename,a.mobile,a.cert_status,a.ctime,b.terrace";
//        $data=M('user','','USER_R')->join('a join dxd_user.user_login b on b.vid=a.vid and b.terrace is not null')->where($where)->LIMIT($page->listfirst, $page->listRows)->field($field)->select();
//        foreach ($data as $k => $v){
//            $data[$k]['ctime']=date('Y-m-d H:i:s',$v['ctime']);
//            switch ($v['cert_status']){
//                case 1:
//                    $data[$k]['cert_status']='未认证';
//                    break;
//                case 2:
//                    $data[$k]['cert_status']='已通过';
//                    break;
//                case 3:
//                    $data[$k]['cert_status']='未通过';
//                    break;
//                case 4:
//                    $data[$k]['cert_status']='认证中';
//                    break;
//                case 5:
//                    $data[$k]['cert_status']='审核中';
//                    break;
//                case 6:
//                    $data[$k]['cert_status']='待审核';
//                    break;
//            }
//        }
//        $this->assign(array(
//            'data'=>$data,
//            'start_time'=>$start,
//            'end_time'=>$end,
//            'fpage' => $fpage,
//        ));
        $this->display();
    }

    /**
     * 应用市场统计导出
     */
    public function APP_market_export()
    {
        $start = I('start_time');
        $end = I('end_time');
        $start_time = strtotime($start);
        $end_time = strtotime($end);
        $where = [];

        if (empty($start) && empty($end)) {
            $this->ajaxReturn(array('code' => '1002', 'result' => '请选择时间'), 'json');
            die;
        }

        $where['a.ctime'] = array('between', array($start_time, $end_time));
//        $where['b.terrace']='vivo';
        $field = "a.truename,a.mobile,a.cert_status,a.ctime,b.terrace";
        $data = M('user', '', 'USER_R')->join('a join dxd_user.user_login b on b.vid=a.vid')->where($where)->field($field)->select();
        require_once './excel/PHPExcel.php'; //导入php文件
        $filePath = './excel/terrace.xlsx'; //打开excel路径
        $PHPReader = new \PHPExcel_Reader_Excel2007(); //默认用excel2007读取excel
        $PHPExcel = $PHPReader->load($filePath); //加载具体文件
        $lineNum = 2;
        foreach ($data as $k => $v) {
            switch ($v['cert_status']) {
                case 1:
                    $cert = '未认证';
                    break;
                case 2:
                    $cert = '已通过';
                    break;
                case 3:
                    $cert = '未通过';
                    break;
                case 4:
                    $cert = '认证中';
                    break;
                case 5:
                    $cert = '审核中';
                    break;
                case 6:
                    $cert = '待审核';
                    break;
            }
            //写入表格
            $PHPExcel->getActiveSheet()->setCellValue('A' . $lineNum, $v['truename']);
            $PHPExcel->getActiveSheet()->setCellValue('B' . $lineNum, $v['mobile']);
            $PHPExcel->getActiveSheet()->setCellValue('C' . $lineNum, $cert);
            $PHPExcel->getActiveSheet()->setCellValue('D' . $lineNum, date('Y-m-d H:i:s', $v['ctime']));
            $PHPExcel->getActiveSheet()->setCellValue('E' . $lineNum, $v['terrace']);
            $lineNum++;
        }
        $PHPExcel->getActiveSheet()->setTitle('应用市场统计');
        $PHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007');//保存类型
        $objWriter->save('./excel/call/terrace.xlsx');
        $url = 'http://nkf.jyyjrfw.com/excel/call/terrace.xlsx';//访问表格路径
        $this->ajaxReturn(array('code' => '1001', 'url' => $url), 'json');
    }

    //客户经理绩效
    public function user_manager()
    {
        $manager = array('934' => '沈阳', '941' => '王意辉');
        $month_s = 1527782400;
        $month_end = 1530374400;
        $borrow = array();//总借款数据
        $repay = array();//总还款数据
        $overdue = array();//总逾期数据
        foreach ($manager as $k => $v) {
            $where = [];
            $where['a.type'] = 2;
            $where['a.admin_id'] = $k;
            $where['a.contact_status'] = 1;
            $where['b.ctime'] = array('between', array($month_s, $month_end));
            $where['b.is_success'] = 2;
            $field = 'a.vid,a.upd_time,b.ctime as b_time,b.money,b.borrow_no';
            $user = M('kefu_ownuser', '', 'KEFU')->join('a join dxd_caiwu.user_borrow b on b.vid=a.vid ')->where($where)->field($field)->select();
            foreach ($user as $k => $v) {
                if ($v['b_time'] > $v['upd_time']) {//借款时间在添加微信之后
                    $borrow[] = array('borrow_no' => $v['borrow_no'], 'vid' => $v['vid'], 'money' => $v['money']);//总的借款数据
                }
            }

            $where = [];
            $where['a.type'] = 2;
            $where['a.admin_id'] = $k;
            $where['a.contact_status'] = 1;
            $where['b.repay_time'] = array('between', array($month_s, $month_end));
            $where['b.status'] = 3;
            $field = 'a.vid,a.upd_time,b.repay_time,b.repay_money,b.borrow_no';
            $user = M('user_repayment', '', 'KEFU')->join('a join dxd_caiwu.user_borrow b on b.vid=a.vid ')->where($where)->field($field)->select();
            foreach ($user as $k => $v) {
                if ($v['repay_time'] > $v['upd_time']) {//还款时间在添加微信之后
                    $repay[] = array('borrow_no' => $v['borrow_no'], 'vid' => $v['vid'], 'money' => $v['repay_money']);//总的借款数据
                }
            }
        }
    }

    /***
     * 外呼语音统计
     */
    public function kf_call_out()
    {
        $admin = I('name');
        $search = I('key');
        $type = I('type');//1 外呼  2 呼入
        $start = I('s_time');
        $end = I('e_time');
        $start_time = strtotime($start);
        $end_time = strtotime($end);
        $where = [];
        $where['calltype'] = array('eq', $type);
        $where['state'] = array('eq', 1);
        $where['admin'] = array('eq', $admin);
        if (!empty($start) && !empty($end)) {
            $where['ctime'] = array('between', array($start_time, $end_time));
        }
        $search_arr = array('key' => $search, 'name' => $admin, 'type' => $type, 's_time' => $start_time, 'e_time' => $end_time);
        if (!empty($search)) {
            $where['calledno|content'] = array('like', $search . '%');
        }
        $tol = M('call_info', '', 'KEFU')->where($where)->count();
        $row = '10';
        $page = new PageController($tol, $row);
        $fpage = $page->fpage();
        $field = 'calledno as mobile,voice_url,state,begin,end,content';
        $data = M('call_info', '', 'KEFU')->where($where)->limit($page->listfirst, $page->listRows)->order('ctime desc')->field($field)->select();
        foreach ($data as $k => $v) {
            $data[$k]['begin'] = date('Y-m-d H:i:s', $v['begin']);
            if (empty($v['begin'])) {
                $data[$k]['begin'] = '--';
            }
            $data[$k]['end'] = date('Y-m-d H:i:s', $v['end']);
            switch ($v['state']) {
                case 1:
                    $data[$k]['state'] = '已接';
                    break;
                case 2:
                    $data[$k]['state'] = '振铃未接听';
                    break;
                case 3:
                    $data[$k]['state'] = 'ivr放弃';
                    break;
                case 4:
                    $data[$k]['state'] = '排队放弃';
                    break;
                case 5:
                    $data[$k]['state'] = '黑名单';
                    break;
            }
            if (empty($v['content'])) {
                $data[$k]['content'] = '---';
            }
        }
        $this->assign(array(
            'data' => $data,
            'fpage' => $fpage,
            'search' => $search_arr,
            'page_first' => $page->listfirst,
            'tol' => $tol
        ));
        $this->display();
    }

    /**
     * 外呼总数
     */
    public function all_call($type,$start_t,$end_t){
        $where = [];
        $where['type'] = $type;
        $where['has_callback'] = 1;
        $where['content'] = 'success';
        $where['ctime'] = ['between', [$start_t, $end_t]];
        $un_cert_total = M('ivr_record', '', 'KEFU')->where($where)->sum('total');
        return $un_cert_total;
    }

    /***
     * 接听总数
     */
    public function tol_listen($type,$start_t,$end_t){

        $where = [];
        $where['type'] = $type;
        $where['has_callback'] = 1;
        $where['content'] = 'success';
        $where['ctime'] = ['between', [$start_t, $end_t]];
        $un_cert_total = M('ivr_record', '', 'KEFU')->where($where)->sum('callback_num');
        return $un_cert_total;
    }

    /**
     * 度言  外呼统计
     */
    public function dy_uncert_statics1()
    {
        $project = I('project', 1);//1 GX 2 马上游呗
        $start = I('start_t');
        $end = I('end_t');
//        $channel=I('channel_num');
        $start_t = strtotime($start);
        $end_t = strtotime($end);

        //注册未认证外呼总数
        if ($project == '1') {
            $like = 'GX%';
        } else if ($project == '2') {
            $like = '游呗%';
        }

        //未认证总数
        $where = [];
        $where['type'] = 1;
        $where['has_callback'] = 1;
        $where['content'] = 'success';
//        $where['name']=['like','GX%'];
        $where['ctime'] = ['between', [$start_t, $end_t]];
        $un_cert_total = M('ivr_record', '', 'KEFU')->where($where)->sum('total');

        //注册拨打 认证率
        $where=[];
        $where['a.type']=1;
        $where['a.outcome']='SUCCESS';
        $call_arr=M('ivr_callback_record','','KEFU')->join('a join dxd_kefu.ivr_callback_record b on b.phone=a.mobile')->where($where)->field('')->select();

//
//        //今日应还款
//        $where = [];
//        $where['type'] = 2;
//        $where['has_callback'] = 1;
//        $where['content'] = 'success';
////        $where['name']=['like','GX%'];
//        $where['ctime'] = ['between', [$start_t, $end_t]];
//        $un_cert_total = M('ivr_record', '', 'KEFU')->where($where)->sum('total');
//

        //未认证接通成功数
        $where = [];
        $where['type'] = 1;
        $where['outcome'] = 'SUCCESS';
        $where['belong'] = $project;
        $where['ctime'] = ['between', [$start_t, $end_t]];
        $un_cert_get_suc = M('ivr_callback_record', '', 'KEFU_R')->where($where)->count();
        //未认证转换数
        $where = [];
        $where['a.type'] = 1;
        $where['a.outcome'] = 'SUCCESS';
        $where['a.belong'] = $project;
        $where['a.ctime'] = ['between', [$start_t, $end_t]];
        $cert_data = M('ivr_callback_record', '', 'KEFU_R')->join('a join dxd_user.user b on b.mobile=a.phone')->where($where)->field('a.phone,a.call_time,b.vid,b.cert_status')->select();
        $first_time = M('user_idcard_img', '', 'USER_R')->where(['vid' => ['in', array_column($cert_data, 'vid')]])->field('vid,ctime')->select();
        $turn_num = 0;
        $passed = 0;
        $refused = 0;
        $certing = 0;
        $in_check = 0;
        $wait_check = 0;
        foreach ($cert_data as $k => $v) {
            //第一项认证时间
            if (!empty($first_time[$v['vid']])) {

                $call_time = $v['call_time'] / 1000;
                if ($call_time < $first_time[$v['vid']]['ctime']) {//打电话之后的认证
                    $turn_num++;
                }
                switch ($v['cert_status']) {
                    case 2:
                        $passed++;
                        break;
                    case 3:
                        $refused++;
                        break;
                    case 4:
                        $certing++;
                        break;
                    case 5:
                        $in_check++;
                        break;
                    case 6:
                        $wait_check++;
                        break;
                }
            }
        }

        $data = array('un_cert_total' => $un_cert_total, 'get_success' => $un_cert_get_suc, 'turn_num' => $turn_num, 'passed' => $passed, 'refused' => $refused, 'certing' => $cert_data, 'in_check' => $in_check, 'wait_check' => $wait_check);
        $this->assign(array(
            'data' => $data,
            'start_t' => $start,
            'end_t' => $end
        ));
        $this->display();
    }

    /**
     *所有外呼计划
     */
    public function dy_all_plane()
    {
        $key = I('key');
        $start = I('start_t');
        $end = I('end_t');
        $type = I('type');
        $start_t = strtotime($start);
        $end_t = strtotime($end);
        $where = [];
        if ($key) {
            $where['name'] = ['like', $key . '%'];
        }
        if ($start_t || $end_t) {
            $where['ctime'] = ['between', [$start_t, $end_t]];
        }
        $where['ctime']=['lt',strtotime(date('Y-m-d',time()))];
        if($type){
            $where['type']=$type;
        }else{
            $where['type']=['notin',[6,7,8,9,10,11]];
        }
        $tol = M('ivr_record', '', 'KEFU')->where($where)->count();
        $row = '30';
        $page = new PageController($tol, $row);
        $fpage = $page->fpage();
        $data = M('ivr_record', '', 'KEFU')->where($where)->field('c_date,campaign_id,name,has_callback,total,callback_num')->limit($page->listfirst, $page->listRows)->order('id desc')->select();
        foreach ($data as $k => $v) {
            if ($v['has_callback'] == '1') {
                $data[$k]['has_callback'] = '已调用';
            } else {
                $data[$k]['has_callback'] = '未调用';
            }
        }

        $this->assign(array(
            'data' => $data,
            'fpage' => $fpage,
            'page_first' => $page->listfirst,
            'tol' => $tol
        ));
        $this->display();
    }


    /**
     * dy-外呼未认证转化
     */
    public function un_cert_turn(){




    }

    /***
     *未认证转化
     */
    public function cert_turn(){

        $start = I('start_t');
        $end = I('end_t');

        if(empty($start)){
            $start=date('Y-m-d',time());
            $end=date('Y-m-d H:i:s',time());
        }
        $start_t = strtotime($start);
        $end_t = strtotime($end);

        $arr=M('admin','','KEFU')->where(['status'=>1,'role_id'=>2])->getField('id',true);
        $admin = M('admin', '', 'KEFU')->where(['id' => array('in', $arr)])->field('id,name')->select();
        $arr = array();
        if($start_t<1539135468){
            $table='kefu_ownuser_copy1';
        }else{
            $table='kefu_ownuser';
        }

        foreach ($admin as $k => $v) {
            $where = [];
            $where['a.admin_id'] = $v['id'];
            $where['b.admin'] = $v['name'];
            $where['a.type'] = 3;
            $where['b.calltype'] = 1;
            $where['b.state'] = 1;
            $where['b.ctime'] = array('between', array($start_t, $end_t));
            $field='b.ctime,b.vid,c.cert_time,c.ctime as register_time';
            $un_cert = M($table, '', 'KEFU')->join('a join dxd_kefu.call_info b on b.vid=a.vid')->join('join dxd_user.user c on c.vid=a.vid and c.cert_status!=1')->where($where)->field($field)->group('b.vid')->select();
            $count = 0;
            $no_count = 0;
            $day_count = 0;
            foreach ($un_cert as $k1 => $v1){
                $date1 = date('Y-m-d', $v1['ctime']);//电话时间
                $cert_time=M('user_examine','','ROM_DQSH')->where(['vid'=>$v1['vid']])->getfield('ctime');//第一认证时间
                $date2= date('Y-m-d',$cert_time);
                if (!empty($cert_time)) {
                    if ($date2 == $date1 && $v1['ctime']<$cert_time) {
                        $day_count++;
                    } else {
                        $count++;
                    }
                }
            }
            $count_arr['day_count'] = $day_count;
            $count_arr['delay_count'] = $count;
            $count_arr['has_no_cert'] = $no_count;
            $count_arr['name'] = $v['name'];

            $where = [];
            $where['a.admin_id'] = $v['id'];
            $where['b.admin'] = $v['name'];
            $where['a.type'] = 3;
            $where['b.calltype'] = 1;
//
            $where['b.ctime'] = array('between', array($start_t, $end_t));

            $count_arr['call_all'] = M($table, '', 'KEFU')->join('a join dxd_kefu.call_info b on b.vid=a.vid')->where($where)->count('DISTINCT a.vid');
            $where['b.state'] = 1;
            $count_arr['call_all_val'] = M($table, '', 'KEFU')->join('a join dxd_kefu.call_info b on b.vid=a.vid')->where($where)->count('DISTINCT a.vid');

            $arr[]=$count_arr;
        }

        $this->assign(array(
            'data' => $arr,
            'start_t' => $start,
            'end_t' => $end
        ));
        $this->display();
    }

    /***
     * wei借款转化
     */
    public function borrow_turn(){

        $start = I('start_t');
        $end = I('end_t');
//        $channel=I('channel_num');
        if(empty($start)){
            $start=date('Y-m-d',time());
            $end=date('Y-m-d H:i:s',time());
        }
        $start_t = strtotime($start);
        $end_t = strtotime($end);

        $arr=M('admin','','KEFU')->where(['status'=>1,'role_id'=>2])->getField('id',true);
        $admin = M('admin', '', 'KEFU')->where(['id' => array('in', $arr)])->field('id,name')->select();
        if($start_t<1538977879){
            $table='kefu_ownuser_copy1';
        }else{
            $table='kefu_ownuser';
        }
        $arr = array();
        foreach ($admin as $k => $v) {
            $where = [];
            $where['a.admin_id'] = $v['id'];
            $where['b.admin'] = $v['name'];
            $where['a.type'] = 1;
            $where['b.calltype'] = 1;
            $where['b.state'] = 1;
            $where['d.ctime'] = array('between', array($start_t, $end_t));
            $where['b.ctime'] = array('between', array($start_t, $end_t));
            $un_cert = M($table, '', 'KEFU')
                ->join('a join dxd_kefu.call_info b on b.vid=a.vid')
                ->join('join dxd_caiwu.user_borrow d on d.vid=a.vid and d.is_success=2')
                ->join('join dxd_user.user c on c.vid=a.vid')
                ->where($where)
                ->field('b.ctime,b.vid,d.ctime as b_time,d.money')
                ->group('b.vid')->select();
            $count = 0;
            $over_count = 0;
            $money = 0;
            foreach ($un_cert as $k1 => $v1) {
                $call_date = date('Y-m-d', $v1['ctime']);
                $borrow_date = date('Y-m-d', $v1['b_time']);
                if ($call_date == $borrow_date) {
                    $count++;
                    $get_money = intval($v1['money'] * 0.005, 2);
                    if ($get_money <= 10) {
                        $get_money = 10;
                    }
                    $money = $money + $get_money;
                } else {
                    $over_count++;
                    $money = $money + 1;
                }
            }

            $count_arr['day_count'] = $count;//当天借款
            $count_arr['delay_count'] = $over_count;//延迟借款
            $where = [];
            $where['a.admin_id'] = $v['id'];
            $where['b.admin'] = $v['name'];
            $where['a.type'] = 1;
            $where['b.calltype'] = 1;
//            $where['b.state'] = 1;
//            $where['d.ctime'] = array('between', array($start_t, $end_t));
            $where['b.ctime'] = array('between', array($start_t, $end_t));

            $count_arr['call_all'] = M($table, '', 'KEFU')
                ->join('a join dxd_kefu.call_info b on b.vid=a.vid')->where($where)->count('DISTINCT a.vid');//总拨打人数

            $where['b.state'] = 1;
            $count_arr['call_all_val'] = M($table, '', 'KEFU')
                ->join('a join dxd_kefu.call_info b on b.vid=a.vid')->where($where)->count('DISTINCT a.vid');//总接听人数
            $count_arr['name']= $v['name'];
            $arr[] = $count_arr;
        }
        $this->assign(array(
            'data' => $arr,
            'start_t' => $start,
            'end_t' => $end
        ));
        $this->display();

    }

    /**
     * 未认证 转化 度言
     */
    public function dy_uncert_statics(){

        $pro=I('project',1);//1共享 3 游呗
        if($pro=='3'){
            $pro_call=5;
        }else{
            $pro_call=1;
        }


        $start_time=I('start_t');//外呼时间
        $end_time=I('end_t');//

        $s=explode(' ',$start_time);
        $e=explode(' ',$end_time);
//        var_dump($s);
//        var_dump($e);
//        $this->display();
//        die;

        if(empty($start_time) || empty($end_time)){
            $this->display();
            die;
        }

        $s_time=strtotime($s[0]);
        $e_time=strtotime($e[0])+24*3600;

        $tol_day=($e_time-$s_time)/(24*3600);
        $arr=array();
        for ($i=1;$i<$tol_day;$i++){
             //检查是否有保存数据

            $st=$s_time+($i*86400);
//            $et=$s_time+(($i+1)*86400);

            $exits=M('dy_tmep_tol','','KEFU_R')->where(['pro_type'=>$pro,'get_time'=>['eq',$st]])->field('total_call,get_total,get_time,in,out,no_cert,pass,refused,wait_check,in_check,ctime,in_cert,pro_type')->find();

            if($exits){
                $arr[]=$exits;
            }else{
                //总的外呼
                $w1=[];
                $w1['has_callback']=1;
                $w1['content']='success';
                $w1['ctime']=['lt',$st];
                $w1['type']=$pro_call;

                $total=M('ivr_record','','KEFU_R')->where($w1)->sum('total');
//                var_dump($pro_call);
//                var_dump($total);
                //接通外呼
                $w2=[];
                    $w2['a.type']=$pro_call;
                $w2['a.outcome']='SUCCESS';
                $w2['a.call_time']=['between',[12,$st*1000]];
                $w2['b.type']=$pro;
                $get_total=M('ivr_callback_record','','KEFU_R')->join('a join dxd_user.user b on b.mobile=a.phone')->where($w2)->count();
                //转化
                $data=M('ivr_callback_record','','KEFU_R')->join('a join dxd_user.user b on b.mobile=a.phone')->where($w2)->field('b.mobile,b.vid,a.call_time,cert_status')->group('b.vid')->select();
                $in24=0;//24小时内去认证数
                $out24=0;//
                $no_cert=0;//为认证
                $pass24=0;
                $refuse24=0;
                $check24=0;//审核中
                $wait_check24=0;//待审核
                $in_cert=0;
                $g_time=M('user_examine','','ROM_DQSH')->where(['vid'=>['in',array_column($data,'vid')]])->getfield('vid,ctime',true);

                foreach ($data as $k=>$v){
                    if($g_time[$v['vid']]<($v['call_time']/1000)+24*3600 && $g_time[$v['vid']]>($v['call_time']/1000) && !empty($g_time[$v['vid']])){
                        $in24++;
                        switch ($v['cert_status']){
                            case 2: $pass24++; break;
                            case 3: $refuse24++; break;
                            case 4: $in_cert++; break;
                            case 5: $check24++; break;
                            case 6: $wait_check24++; break;
                        }
                    }else if($g_time[$v['vid']]){
                        $out24++;
                    }else{
                        $no_cert++;
                    }
                }
                $add=array('total_call'=>$total,'get_total'=>$get_total,'get_time'=>$st,'in'=>$in24,'out'=>$out24,'no_cert'=>$no_cert,'pass'=>$pass24,'refused'=>$refuse24,'wait_check'=>$wait_check24,'in_check'=>$check24,'ctime'=>time(),'in_cert'=>$in_cert,'pro_type'=>$pro);
                $arr[]=$add;
                M('dy_tmep_tol','','KEFU')->add($add);
            }
        }


        foreach ($arr as $k=>$v){
            $arr[$k]['get_time']=date('Y-m-d',$v['get_time']);

        }

        $this->assign(['data'=>$arr]);
        $this->display();
    }

    /**
     * 每天的接通率
     */
    public function day_call_rate(){

//        $key = I('key');
        $start = I('start_t');
        $end = I('end_t');
//        $type = I('type');
        $start_t = strtotime($start);
        $end_t = strtotime($end);
        $where = [];
        if ($start_t || $end_t) {
            $where['time'] = ['between', [$start_t, $end_t]];
        }
//        $where['time']=['lt',strtotime(date('Y-m-d',time()))];

        $tol = M('kefu_day_statics', '', 'KEFU')->where($where)->count();
        $row = '30';
        $page = new PageController($tol, $row);
        $fpage = $page->fpage();
        $data = M('kefu_day_statics', '', 'KEFU')->where($where)->field('uncert_rate,today_pay_rate,recent_uncert_rate,cert_unfinish,overdue_rate,time')->limit($page->listfirst, $page->listRows)->order('id desc')->select();

        foreach ($data as $k=>$v){

            $data[$k]['ctime']=date('Y-m-d',$v['time']);

        }

        $this->assign(array(
            'data' => $data,
            'fpage' => $fpage,
            'page_first' => $page->listfirst,
            'tol' => $tol
        ));
        $this->display();

    }


}